import sqlite3 as db
import os
from lib.MySequence import MySequence

class InitDB(MySequence):
    """docstring for InitDB
初始化数据库
    newDatabase   新建数据库
    """
    def __init__(self, path):
        super().__init__(path)
        
    def createMyTables(self):
        cu= self.cu
        conn = self.conn
        sql_createTable_bank="""
create table bank (
   id                   INT          unique       not null,
   name                 vchar2(90)            not null,
   cardNum              vchar2(30)    primary key       not null,
   balance              FLOAT8               not null,
   isCredit             INT4                 not null,
   creditBalance        FLOAT8               null,
   accountDay           int                  null,
   interestFree         INT                 null,
   pic                  blob                 null,
   brand                vchar2(90)           null,
   expireDay            date                 not null,
   commands             clob                 null,
   isDeleted            int                  not null default '0'
)
    """
        sql_createTbale_channel= """
create table channel (
   id                   INT           primary key      not null,
   level                int                  not null,
   channelNameP         vchar(255)           not null,
   channelNameS         vchar(255)           not null,
   isSelf               bool                 not null,
   type                 int                  not null,
   tax                  FLOAT               not null,
   bankNum              vchar(30)            null,
   pic                  blob                 null,
   commands             clob                 null,
   isDeleted            int                  not null default '0'
)
    """
        sql_crateTable_accountBook = """
create table accountBook (
   id                   INT         primary key       not null,
   date                 DATE                 not null,
   bankNum              vchar(30)            not null,
   channeName           vchar(255)           not null,
   amount               float                not null,
   booked               int                  not null,
   bookedDate           date                 not null,
   isExpense            int                  not null,
   commands             clob                 null,
   isDeleted            int                  not null default '0'
)
    """
        try:
            self.createSequence('bank_id_seq','bank')
        except Exception as ex:
            raise ex
            print("出错啦 %s" % ex)
        try:
            self.createSequence('channel_id_seq','channel')
        except Exception as ex:
            raise ex
            print("出错啦 %s" % ex)
        try:
            self.createSequence('accountBook_id_seq','accountBook')
        except Exception as ex:
            raise ex
            print("出错啦 %s" % ex)
        try:
            cu.execute(sql_createTable_bank)
            cu.execute(sql_createTbale_channel)
            cu.execute(sql_crateTable_accountBook)
            conn.commit()
        except db.OperationalError as ex:
            print("出错啦 %s" % ex)
            raise ex
            return 0
        return 1


    def newDatabase(self):
        conn = self.conn
        if self.createMySequenceTable():
            print("建立sequence成功")
        else:
            print("建立sequence失败")
        if self.createMyTables():
            print("建立 tables 成功")
        else:
            print("建立 tables 失败")
        conn.commit()
        conn.close()





